#!/usr/bin/env python
# -*- coding: utf-8 -*-
# @Date    : 2015-06-22 01:14:23
# @Author  : shitao.tommy (hero007asd@gmail.com)
# @Link    : http://example.org
# @Version : $Id$

# from framework.db.basedb import MysqlDb
from framework.db.mysql import BaseDb
from tornado import gen
from lib import log

class Student(BaseDb):

    @gen.coroutine
    def select_dorm(self, Fschool_id):
        sql = 'SELECT Fid, Fdorm_name, Fremark, Fcreate_time FROM tb_dorm WHERE Fschool_id = %s'

        ret = yield self.executejson(sql, (Fschool_id))
        raise gen.Return(ret)

    @gen.coroutine
    def select_room(self, Fdorm_id, Fschool_id):
        sql = 'SELECT Fid, Fname, Fqrcode, Fcreate_time FROM tb_room WHERE Fschool_id = %s AND Fdorm_id = %s'

        ret = yield self.executejson(sql, (Fschool_id, Fdorm_id))
        raise gen.Return(ret)

    @gen.coroutine
    def select_school(self, Fschool_id):
        sql = 'SELECT Fid, Fname, Faddress, Fcity_id, Fis_branch, Fremark, Fcreate_time FROM tb_school WHERE Fschool_id = %s'

        ret = yield self.executejson(sql, (Fschool_id))
        raise gen.Return(ret)

    @gen.coroutine
    def insert_student(self, student_data, student_info):

        ret = yield self.insert("tb_student", student_info)
        student_data["Fuser_id"] = ret
        ret = yield self.insert("tb_user_login", student_data)

        self.commit()

        raise gen.Return(ret)

    @gen.coroutine
    def select_student(self, school_id, where, page, offset):
        sql = '''SELECT a.Fid, a.Fname, a.Fno, b.Fdorm_name, c.Fname Froom
                , a.Ftel, a.Fage, a.Favatar, a.Fstatus, a.Fcreate_time
                FROM tb_student a 
                LEFT JOIN tb_dorm b 
                ON a.Fdorm_id = b.Fid
                LEFT JOIN tb_room c 
                ON a.Froom = c.Fid
                WHERE a.Fschool_id = %s %s 
                LIMIT %s, %s'''
        count_sql = 'SELECT count(Fid) FROM tb_student WHERE Fschool_id = %s'

        wheres = ''
        for i in where.keys():
            if where[i] != '':
                wheres += " AND a." + i + "='" + str(where[i]) + "'"
        sql = sql % ('%s', wheres, '%s', '%s')

        ret = yield self.executejson(sql, (school_id, page, offset))
        count = yield self.execute(count_sql, (school_id))

        raise gen.Return((ret, count[0][0]))

    @gen.coroutine
    def find_student(self, Fid):
        sql = '''SELECT a.Fid, a.Fname, a.Fno, a.Fdorm_id, a.Froom
                , a.Ftel, a.Fage, a.Favatar, a.Fstatus, a.Fcreate_time
                , b.Fuser_name, b.Fpwd
                FROM tb_student a 
                LEFT JOIN tb_user_login b 
                ON b.Fuser_id = a.Fid
                WHERE a.Fid = %s '''

        ret = yield self.executejson(sql, (Fid))

        if len(ret) >= 1:
            raise gen.Return(ret[0])
        else:
            raise gen.Return({})

    @gen.coroutine
    def del_student(self, Fid):
        sql = 'DELETE FROM tb_student WHERE Fid = %s'

        ret = yield self.upsert_commit(sql, (Fid))

        raise gen.Return(ret)

    @gen.coroutine
    def update_student(self, Fid, Fuser_name, arg):
        student_sql = '''UPDATE tb_student SET %s WHERE Fid = %s'''
        user_sql = 'UPDATE tb_user_login SET Fuser_name = %s WHERE Fuser_id = %s'

        update_data = ''
        for i in arg.keys():
            update_data += i + '=%s,'

        student_sql = student_sql % (update_data[0:-1], '%s')
        print student_sql

        data = arg.values()
        data.append(Fid)

        ret = yield self.upsert(student_sql, tuple(data))
        user = yield self.upsert(user_sql, (Fuser_name, Fid))

        self.commit()

        raise gen.Return((ret, user))

    @gen.coroutine
    def set_status(self, Fid, status):
        sql = 'UPDATE tb_student SET Fstatus = %s WHERE Fid = %s'

        ret = yield self.upsert_commit(sql, (status, Fid))

        raise gen.Return(ret)

    @gen.coroutine
    def set_istatus(self, status, *ids):
        sql = 'UPDATE tb_student SET Fstatus = %s WHERE Fid in %s'

        ret = yield self.upsert_commit(sql, (status, ids))

        raise gen.Return(ret)